package com.wz.project.system.dayReport.repository;

import com.wz.project.system.dayReport.entity.DayReport;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;


public interface DayReportDao extends JpaRepository<DayReport,Integer>, JpaSpecificationExecutor<DayReport> {
    /**
     * 根据不同的字段进行条件查询
     * @param staffName
     * @param staffStation
     * @param staffStatus
     * @param startTime
     * @param updateTime
     * @return
     */

    @Query(value = "select * from sys_day_report where staff_name like %?1% " +
                    "and staff_station like %?2% " +
                    "and staff_status like %?3% " +
                    "and start_time>=?4 and start_time<=?5",nativeQuery = true)
    List<DayReport> findAll(String staffName, String staffStation, String staffStatus, Object startTime, Object updateTime);

    @Query(value = "select  d.report_id,d.staff_name,d.start_time,d.staff_status,d.finish_work,d.notdo_work," +
            "       d.staff_progress,d.today_plan,d.update_time,d.staff_station,d.flag from sys_day_report d" +
            "    where  d.staff_name like %?1% " +
                    "and d.staff_station like %?2% " +
                    "and d.staff_status like %?3% " +
                    "and d.start_time>=?4 and d.start_time<=?5 order by d.staff_name",nativeQuery = true)
    List<DayReport> findAllGroup(String staffName, String staffStation, String staffStatus, Object startTime, Object updateTime);


    /**
     * 根据日期查询
     * @param format
     * @return
     */
    @Query(value = "select count (1) from sys_day_report where staff_name=?2 and CONVERT(varchar(100), start_time, 23)=?1 ",nativeQuery = true)
    int findByStartTimeCount(String format,String name);

    //
    @Query(value = "select TOP 1 * from sys_day_report " +
            "where staff_name =?1 AND" +
            " start_time >convert(datetime,convert(char(20),dateadd(day,-0,getdate()),102))  " +
            "ORDER BY start_time  DESC",nativeQuery = true)
    DayReport findTopByStaffName(String staffName);

    @Query(value = "SELECT COUNT(*) from (select r.*,e.dept_id FROM sys_day_report r " +
            "left join sys_employee e on r.staff_name = e.name) AS q " +
            "WHERE staff_name = ?1 AND flag = '1'" +
            "AND datediff(month,[start_time],getdate())=0  ",nativeQuery = true)
    Integer findFlagYByName(String name);

    @Query(value = "SELECT COUNT(*) from (select r.*,e.dept_id FROM sys_day_report r " +
            "left join sys_employee e on r.staff_name = e.name) AS q " +
            "WHERE staff_name = ?1 AND flag = '0'" +
            " AND datediff(month,[start_time],getdate())=0  ",nativeQuery = true)
    Integer findFlagNByName(String name);

    @Query(value = "SELECT COUNT(*) from (select r.*,e.dept_id FROM sys_day_report r " +
            "            left join sys_employee e on r.staff_name = e.name) AS q " +
            "            WHERE  flag = ?1" +
            "            AND convert(varchar,q.start_time) " +
            "            between convert(varchar,dateadd(day,?2,getdate())) " +
            "            and convert(varchar,dateadd(day,?3,getdate()))",nativeQuery = true)
    Integer findFlagYByNameWeek(String falg,Integer daybf,Integer dayaf);

    @Query(value = "SELECT COUNT(*) from (select r.*,e.dept_id FROM sys_day_report r " +
            "left join sys_employee e on r.staff_name = e.name) AS q " +
            "WHERE  flag = ?1" +
            " AND datediff(month,[start_time],getdate())=0  ",nativeQuery = true)
    Integer findFlagNByNameMonthCount(String flag);
    @Query(value = "SELECT COUNT(*) from (select r.*,e.dept_id FROM sys_day_report r " +
            "left join sys_employee e on r.staff_name = e.name) AS q " +
            "WHERE  flag = ?1" +
            " AND datediff(week,[start_time],getdate())=0  ",nativeQuery = true)
    Integer findFlagNByNameWeekCount(String flag);

//    @Query(value = "SELECT COUNT(*) from (select r.*,e.dept_id FROM sys_day_report r " +
//            "left join sys_employee e on r.staff_name = e.name) AS q " +
//            "WHERE q.staff_name = :#{#e.staffName} or :#{#e.staffName} is null " +
//            "AND  q.dept_id = :#{#e.deptId} or :#{#e.deptId} is null "+
//            "AND flag = ?2" +
//            "AND datediff(month,[start_time],getdate())=0  ",nativeQuery = true)
//    Integer findFlagNByNameMonth(@Param("e") Employee employee,String falg);


}

